package yu.zhou.njupt.importdata.core;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;

import com.google.common.base.Joiner;

import yu.zhou.njupt.importdata.entity.Column;
import yu.zhou.njupt.importdata.entity.ImportResult;
import yu.zhou.njupt.importdata.entity.enumtype.CreateTable;
import yu.zhou.njupt.importdata.entity.enumtype.DoTruncate;
import yu.zhou.njupt.importdata.entity.enumtype.NewImport;
import yu.zhou.njupt.importdata.util.RegexUtil;
import yu.zhou.njupt.importdata.util.SqlUtil;

public class Importer {
    
    protected Connection conn = null;
    protected CreateTable createTable;
    protected DoTruncate doTruncate;
    protected NewImport newImport;
    protected List<Integer> columnIndexList;
    protected List<Column> columnList;
    protected Map<Integer, Column> columnMap;
    protected String sql;
    protected int titleNum;
    protected int batch_num;
    
    public Importer(Connection conn, DoTruncate doTruncate, NewImport newImport, CreateTable createTable, int titleNum) throws Exception {
        this.conn = conn;
        this.doTruncate = doTruncate;
        this.newImport = newImport;
        this.createTable = createTable;
        this.titleNum = titleNum;
        
        Statement s = conn.createStatement();
        ResultSet rs = s.executeQuery("select nvl(max(batch_num), 0) from zz_table");
        int n = -1;
        if (rs.next()) {
            n = rs.getInt(1);
        }
        if (newImport == NewImport.TRUE) {
            batch_num = n + 1;
        } else {
            batch_num = n;
        }
    }

    /** 将数据从数据源中导入到数据库(核心方法) */
    public ImportResult importData(File sourceFile,
            List<String> disableConsStrSet, List<String> enableConsStrSet, 
            List<String> disableIndexesStrSet, List<String> enableIndexesStrSet){
        return null;
    };
    
    protected boolean hasDealt(String filename, String sheetName) throws Exception {
        boolean result = false;

        if (newImport == NewImport.FALSE) {
            String str1 = filename.toLowerCase() + "-" + (sheetName == null ? "" : sheetName.toLowerCase());
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(SqlUtil.getSqlForHasDealtFile());
            while (rs.next()) {
                if (str1.equals(rs.getString(1))) {
                    result = true;
                    break;
                }
            }
        }

        return result;
    }

    protected void createTable(String tableName, List<Column> columnList) throws Exception {
        Statement s = conn.createStatement();
        int num = -1;
        
        ResultSet rs = s.executeQuery("select count(1) from user_tables where table_name = '" + tableName.toUpperCase() + "'");
        
        if (rs.next()) {
            num = rs.getInt(1);
        }
        if (num == 1) {
            System.out.println("    表" + tableName.toUpperCase() + "已存在");
        } else {
            String sql = "create table \"" + tableName.toUpperCase() + "\"(";
            for (Column column : columnList) {
                sql += "\"" + column.getName().toUpperCase() + "\" " + column.getType() + ",";
            }
            sql = sql.substring(0, sql.length() - 1) + ")";
            s.execute(sql);
            System.out.println("    表" + tableName.toUpperCase() + "创建成功");
        }
        
        DbUtils.closeQuietly(s);
    }
    
    protected void getSql(String tableName, List<String> cellNameList) throws Exception {
        columnList = new ArrayList<Column>();
        columnMap = new HashMap<Integer, Column>();
        List<String> columnNameList = new ArrayList<String>();
        List<String> placeholderList = new ArrayList<String>();
        
        for (int i = 0; i < cellNameList.size(); i++) {
            String cellName = cellNameList.get(i);
            String columnName = RegexUtil.getValidName(cellName).toUpperCase();
            String dataType = RegexUtil.getDataTypeStr(cellName);
            String columnType = null;
            String columnType2 = null;
            if (dataType == null) {
            	columnType = "VARCHAR2(4000 char)";
                columnType2 = null;
            } else if (dataType.toUpperCase().startsWith("V ")) {
                columnType = "VARCHAR2(" + dataType.substring(2) + " char)";
                columnType2 = null;
            } else if (dataType.toUpperCase().startsWith("N ")) {
            	columnType = "NUMBER(" + dataType.substring(2) + ")";
                columnType2 = "NUMBER";
            } else if (dataType.toUpperCase().startsWith("DATE ")) {
                columnType = "DATE";
                columnType2 = dataType;
            }
            Column column = new Column();
            column.setName(columnName);
            column.setType(columnType);
            column.setType2(columnType2);
            
            columnNameList.add(columnName);
            columnList.add(column);
            columnMap.put(columnIndexList.get(i), column);
            placeholderList.add("?");
        }
        
        if (createTable == CreateTable.TRUE) {
            createTable(tableName, columnList);
        }

       sql = "insert into " + tableName + " (" + Joiner.on(',').join(columnNameList) + ") " + "values (" + Joiner.on(',').join(placeholderList) + ")";
       System.out.println("    导入语句：" + sql);
    }
   
   /** 根据数据类型，对单元格内的字符串进行转换 */
   protected Object getValue(String originalStr, Integer columnIndex) throws Exception {
       Object value = null;
       
       if (originalStr != null) {
           Column column = columnMap.get(columnIndex);
           String columnType2 = column.getType2();
           
           if (columnType2 == null) {
        	   value = originalStr;
           } else if (columnType2.equals("NUMBER")) {
               if (originalStr != null && !originalStr.trim().equals("")) {
                   value = Double.valueOf(originalStr.trim());
               }
           } else {
               if (originalStr != null && !originalStr.trim().equals("")) {
                   value = new Timestamp(new SimpleDateFormat(columnType2.substring(5)).parse(originalStr.trim()).getTime());
               }
           }

       }
       
       return value;
   }
    
    /** 向一张表导入数据前所需做的操作 */
    protected void doBeforeImport(String tableName, List<String> disableConsStrSet,
            List<String> enableConsStrSet, List<String> disableIndexesStrSet,
            List<String> enableIndexesStrSet) throws Exception {
        Statement s = conn.createStatement();
        
        /* 禁用表上的约束，并保存禁用、启用约束的语句 */
        PreparedStatement ps = conn.prepareStatement(SqlUtil.getSqlForCons());
        ps.setString(1, tableName);
        ResultSet rs = ps.executeQuery();
        String disableObjStr = null;
        String enableObjStr = null;
        
        while (rs.next()) {
            disableObjStr = rs.getString(1);
            enableObjStr= rs.getString(2);
            
            s.execute(disableObjStr);
            
            if (!enableConsStrSet.contains(enableObjStr)) {
                disableConsStrSet.add(disableObjStr);
                enableConsStrSet.add(enableObjStr);
            }
        }
        
        /* 删除表上的索引，并保存删除、重建索引的语句 */
        ps = conn.prepareStatement(SqlUtil.getSqlForIndexes());
        ps.setString(1, tableName);
        rs = ps.executeQuery();
        
        while (rs.next()) {
            disableObjStr = rs.getString(1);
            enableObjStr= rs.getString(2);
            
            s.execute(disableObjStr); // 禁用索引
            
            disableIndexesStrSet.add(disableObjStr); // 保存禁用索引语句
            enableIndexesStrSet.add(enableObjStr); // 保存重建索引语句
        }
        
        if (doTruncate == DoTruncate.TRUE) {
            s.execute("truncate table " + tableName);
            
            DbUtils.closeQuietly(s);
        }
        
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(s);
    }
    
    /** 向一张表导入数据后所需做的操作 */
    protected void doAfterImport(String tableName, String filename, String sheetName) throws Exception {
        /* 更新日志表(z_table) */
        String sql = SqlUtil.getSqlForUpdateLog();
        sql = MessageFormat.format(sql, 
                "'" + tableName.toUpperCase() + "'", 
                "'" + filename.toLowerCase() + "-" + (sheetName == null ? "" : sheetName.toLowerCase()) + "'",
                batch_num);
                
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, tableName);
        ps.executeUpdate();

        DbUtils.closeQuietly(ps);
    }
    
}